class Course {
  constructor(db) {
    this.db = db;
  }

  /**
   * Initialize the course tables
   */
  async initTables() {
    // Create courses table
    const courseSql = `
      CREATE TABLE IF NOT EXISTS courses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        cover_image TEXT,
        category TEXT,
        level INTEGER DEFAULT 1,
        total_lessons INTEGER DEFAULT 0,
        estimated_hours INTEGER DEFAULT 0,
        prerequisites TEXT,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        is_published BOOLEAN DEFAULT FALSE
      )
    `;

    // Create curriculum table
    const curriculumSql = `
      CREATE TABLE IF NOT EXISTS curriculum (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_id INTEGER NOT NULL,
        parent_id INTEGER,
        title TEXT NOT NULL,
        description TEXT,
        order_num INTEGER DEFAULT 0,
        type TEXT NOT NULL,
        content_id INTEGER,
        created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (course_id) REFERENCES courses(id),
        FOREIGN KEY (parent_id) REFERENCES curriculum(id)
      )
    `;

    // Create course_progress table
    const progressSql = `
      CREATE TABLE IF NOT EXISTS course_progress (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_id INTEGER NOT NULL,
        curriculum_id INTEGER NOT NULL,
        status TEXT DEFAULT 'not_started',
        progress INTEGER DEFAULT 0,
        started_at DATETIME,
        completed_at DATETIME,
        last_accessed DATETIME DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (course_id) REFERENCES courses(id),
        FOREIGN KEY (curriculum_id) REFERENCES curriculum(id)
      )
    `;

    // Create indexes for better performance
    const indexes = [
      'CREATE INDEX IF NOT EXISTS idx_curriculum_course ON curriculum(course_id)',
      'CREATE INDEX IF NOT EXISTS idx_curriculum_parent ON curriculum(parent_id)',
      'CREATE INDEX IF NOT EXISTS idx_progress_course ON course_progress(course_id)',
    ];

    try {
      await this.db.run(courseSql);
      await this.db.run(curriculumSql);
      await this.db.run(progressSql);

      for (const indexSql of indexes) {
        await this.db.run(indexSql);
      }

      console.log('Course tables initialized');
    } catch (err) {
      console.error('Error initializing course tables:', err);
    }
  }

  /**
   * Create a new course
   * @param {Object} courseData - Course data to insert
   * @returns {Promise} - Promise that resolves with the inserted course ID
   */
  async createCourse(courseData) {
    const sql = `
      INSERT INTO courses (title, description, cover_image, category, level, 
                          total_lessons, estimated_hours, prerequisites, is_published)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
      courseData.title,
      courseData.description || '',
      courseData.cover_image || '',
      courseData.category || '',
      courseData.level || 1,
      courseData.total_lessons || 0,
      courseData.estimated_hours || 0,
      courseData.prerequisites ? JSON.stringify(courseData.prerequisites) : '[]',
      courseData.is_published ? 1 : 0,
    ];

    try {
      const result = await this.db.run(sql, params);
      return result.id;
    } catch (err) {
      console.error('Error creating course:', err);
      throw err;
    }
  }

  /**
   * Get a course by ID
   * @param {number} id - Course ID
   * @returns {Promise} - Promise that resolves with the course data
   */
  async getCourseById(id) {
    const sql = 'SELECT * FROM courses WHERE id = ?';
    try {
      const course = await this.db.get(sql, [id]);
      if (course && course.prerequisites) {
        course.prerequisites = JSON.parse(course.prerequisites);
      }
      return course;
    } catch (err) {
      console.error('Error getting course by ID:', err);
      throw err;
    }
  }

  /**
   * Get all courses
   * @returns {Promise} - Promise that resolves with all courses
   */
  async getAllCourses() {
    const sql = 'SELECT * FROM courses ORDER BY created_at DESC';
    try {
      const courses = await this.db.all(sql);
      return courses.map(course => {
        if (course.prerequisites) {
          course.prerequisites = JSON.parse(course.prerequisites);
        }
        return course;
      });
    } catch (err) {
      console.error('Error getting all courses:', err);
      throw err;
    }
  }

  /**
   * Create a curriculum item
   * @param {Object} curriculumData - Curriculum data to insert
   * @returns {Promise} - Promise that resolves with the inserted curriculum ID
   */
  async createCurriculumItem(curriculumData) {
    const sql = `
      INSERT INTO curriculum (course_id, parent_id, title, description, order_num, type, content_id)
      VALUES (?, ?, ?, ?, ?, ?, ?)
    `;

    const params = [
      curriculumData.course_id,
      curriculumData.parent_id || null,
      curriculumData.title,
      curriculumData.description || '',
      curriculumData.order_num || 0,
      curriculumData.type,
      curriculumData.content_id || null,
    ];

    try {
      const result = await this.db.run(sql, params);
      return result.id;
    } catch (err) {
      console.error('Error creating curriculum item:', err);
      throw err;
    }
  }

  /**
   * Get curriculum items for a course
   * @param {number} courseId - Course ID
   * @param {number|null} parentId - Parent ID (null for root items)
   * @returns {Promise} - Promise that resolves with curriculum items
   */
  async getCurriculumItems(courseId, parentId = null) {
    let sql, params;
    if (parentId === null) {
      sql = 'SELECT * FROM curriculum WHERE course_id = ? AND parent_id IS NULL ORDER BY order_num';
      params = [courseId];
    } else {
      sql = 'SELECT * FROM curriculum WHERE course_id = ? AND parent_id = ? ORDER BY order_num';
      params = [courseId, parentId];
    }

    try {
      return await this.db.all(sql, params);
    } catch (err) {
      console.error('Error getting curriculum items:', err);
      throw err;
    }
  }
}

module.exports = Course;
